﻿using PIMS_Interface.Interface;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using System.Windows.Forms;
using static NPOI.HSSF.Util.HSSFColor;

namespace glb
{
    /// <summary>  
    /// 说明：这是一个针对System.Data.SQLite的数据库常规操作封装的通用类。  
    /// </summary>  
    public class SQLiteDBHelper
    {
        private static string dbfile = string.Concat(new string[]
        {
              AppDomain.CurrentDomain.BaseDirectory,
              "Log\\",
             // DateTime.Now.ToString("yyyy-MM-dd"),
             "PIMSLog",
              ".db"
        });

        private static string dbfileApi = string.Concat(new string[]
        {
              AppDomain.CurrentDomain.BaseDirectory,
              "Log\\",
             // DateTime.Now.ToString("yyyy-MM-dd"),
             "PIMSApiLog",
              ".db"
        });

        /// <summary>
        /// SQLite数据库文件路径
        /// </summary>
        private static string connectionString = "Data Source=" + dbfile;


        private static object obj = new object();

        /// <summary>  
        /// 创建SQLite数据库文件  
        /// </summary>  
        /// <param name="dbPath">要创建的SQLite数据库文件路径</param>  
        public static void CreateDB(string dbPath)
        {
            using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + dbPath))
            {
                connection.Open();
                using (SQLiteCommand command = new SQLiteCommand(connection))
                {
                    //command.CommandText = "CREATE TABLE DBLog(id integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,)";
                    command.CommandText = string.Concat(new string[]
                    {
                       //"CREATE TABLE DBLog(id integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,LogLevel TEXT,LogMessage TEXT,LogDate TIMESTAMP)"
                       "CREATE TABLE DBLog(id integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,LogLevel TEXT,LogGroup TEXT,plcIndex integer,LogMessage TEXT,LogDate TIMESTAMP)"
                    });
                    command.ExecuteNonQuery();
                    //command.CommandText = "DROP TABLE Demo";
                    //command.ExecuteNonQuery();
                }
            }
        }
        /// <summary>  
        /// 对SQLite数据库执行增删改操作，返回受影响的行数。  
        /// </summary>  
        /// <param name="sql">要执行的增删改的SQL语句</param>  
        /// <param name="parameters">执行增删改语句所需要的参数，参数必须以它们在SQL语句中的顺序为准</param>  
        /// <returns></returns>  
        public static int ExecuteNonQuery(string sql, SQLiteParameter[] parameters)
        {
            int affectedRows = 0;
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                using (DbTransaction transaction = connection.BeginTransaction())
                {
                    using (SQLiteCommand command = new SQLiteCommand(connection))
                    {
                        command.CommandText = sql;
                        if (parameters != null)
                        {
                            command.Parameters.AddRange(parameters);
                        }
                        affectedRows = command.ExecuteNonQuery();
                    }
                    transaction.Commit();
                }
            }
            return affectedRows;
        }

        public static int ExecuteNonQuery(string sql, SQLiteParameter[] parameters, string connectStr)
        {
            int affectedRows = 0;
            using (SQLiteConnection connection = new SQLiteConnection(connectStr))
            {
                connection.Open();
                using (DbTransaction transaction = connection.BeginTransaction())
                {
                    using (SQLiteCommand command = new SQLiteCommand(connection))
                    {
                        command.CommandText = sql;
                        if (parameters != null)
                        {
                            command.Parameters.AddRange(parameters);
                        }
                        affectedRows = command.ExecuteNonQuery();
                    }
                    transaction.Commit();
                }
            }
            return affectedRows;
        }
        /// <summary>  
        /// 执行一个查询语句，返回一个关联的SQLiteDataReader实例  
        /// </summary>  
        /// <param name="sql">要执行的查询语句</param>  
        /// <param name="parameters">执行SQL查询语句所需要的参数，参数必须以它们在SQL语句中的顺序为准</param>  
        /// <returns></returns>  
        public static SQLiteDataReader ExecuteReader(string sql, SQLiteParameter[] parameters)
        {
            SQLiteConnection connection = new SQLiteConnection(connectionString);
            SQLiteCommand command = new SQLiteCommand(sql, connection);
            if (parameters != null)
            {
                command.Parameters.AddRange(parameters);
            }
            connection.Open();
            return command.ExecuteReader(CommandBehavior.CloseConnection);
        }
        /// <summary>  
        /// 执行一个查询语句，返回查询结果的第一行第一列  
        /// </summary>  
        /// <param name="sql">要执行的查询语句</param>  
        /// <param name="parameters">执行SQL查询语句所需要的参数，参数必须以它们在SQL语句中的顺序为准</param>  
        /// <returns></returns>  
        public static DataTable ExecuteScalar(string sql, SQLiteParameter[] parameters)
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                using (SQLiteCommand command = new SQLiteCommand(sql, connection))
                {
                    if (parameters != null)
                    {
                        command.Parameters.AddRange(parameters);
                    }
                    SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
                    DataTable data = new DataTable();
                    adapter.Fill(data);
                    return data;
                }
            }
        }

        public static DataTable ExecuteScalar(string sql, SQLiteParameter[] parameters, string connectStr)
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectStr))
            {
                using (SQLiteCommand command = new SQLiteCommand(sql, connection))
                {
                    if (parameters != null)
                    {
                        command.Parameters.AddRange(parameters);
                    }
                    SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
                    DataTable data = new DataTable();
                    adapter.Fill(data);
                    return data;
                }
            }
        }
        /// <summary>  
        /// 查询数据库中的所有数据类型信息  
        /// </summary>  
        /// <returns></returns>  
        public static DataTable GetSchema()
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                DataTable data = connection.GetSchema("TABLES");
                connection.Close();
                //foreach (DataColumn column in data.Columns)  
                //{  
                //  Console.WriteLine(column.ColumnName);  
                //}  
                return data;
            }
        }

        public static DataTable Query(string sql)
        {
            return ExecuteScalar(sql, null);
        }

        public static DataTable Query(string sql, string dbfile)
        {
            return ExecuteScalar(sql, null, "Data Source=" + dbfile);
        }

        public static int ExecuteNonQuery(string sql)
        {
            return ExecuteNonQuery(sql, null);
        }

        private static void CheckDBExist()
        {
            if (!File.Exists(dbfile))
            {
                CreateDB(dbfile);

                DBClear();
            }
        }

        public static void DBLog(string msg, string logLevel = "Info")
        {
            lock (obj)
            {
                CheckDBExist();

                string cmdSql = string.Concat(new string[]
               {
                   "insert into DBLog(LogLevel,LogMessage,LogDate) values('",
                   logLevel + "','",
                   msg + "','",
                   DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"),
                   "')"
               });
                try
                {
                    ExecuteNonQuery(cmdSql);
                }
                catch
                {
                }
            }

        }

        public static void DBLog(string msg, string logGroup, int plcIndex,DateTime time,string logLevel = "Info")
        {
            lock (obj)
            {
                CheckDBExist();

                string cmdSql = string.Concat(new string[]
               {
                   "insert into DBLog(LogLevel,LogGroup,plcIndex,LogMessage,LogDate) values('",
                   logLevel + "','",
                   logGroup + "','",
                   plcIndex + "','",
                   msg.Replace("'","") + "','",
                   time.ToString("yyyy-MM-dd HH:mm:ss"),
                   "')"
               });
                try
                {
                    ExecuteNonQuery(cmdSql);
                }
                catch(Exception ex)
                {
                    Console.WriteLine(ex.ToString());
                }
            }

        }

        public static void DBLog(Exception ex)
        {
            DBLog(ex.Message + " " + ex.StackTrace, "Error");
        }

        public static void DBDelete(int days)
        {
            lock (obj)
            {
                // string cmdSql = $"delete from DBLog where date('now','- {days} day') >= date(LogDate)"; //忽略时间精度，直接比较日期，删除N天前的数据
                string cmdSql = $"delete from DBLog where julianday('now') - julianday(LogDate) >= {days}"; //计算时间精度，比较日期和时间，删除N天前的数据。
                try
                {
                    ExecuteNonQuery(cmdSql);
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.ToString());
                }
            }
        }

        /// <summary>
        /// 保存30天内的文件
        /// </summary>
        public static void DBClear()
        {
            DirectoryInfo dyInfo = new DirectoryInfo(Application.StartupPath + "\\Log");
            //获取文件夹下所有的文件
            foreach (FileInfo feInfo in dyInfo.GetFiles())
            {
                //判断文件日期是否小于今天，是则删除
                if (feInfo.CreationTime < DateTime.Today.AddDays(-30))
                    feInfo.Delete();
            }
        }

    }


}
